insert
  overwrite table jms_dm.dm_false_sign_effic_sum_dt partition(dt)
select
  network.zone_id area_info_id,
  network.zone_code area_info_code,
  network.zone_name area_info_desc,
  a.network_code,
  network.name network_name,
  network.provider_id,
  network.provider_desc,
  network.city_id,
  network.city_desc,
  network.area_id,
  network.area_desc,
  nvl(network.virt_code, network.agent_code) virt_code,
  nvl(network.virt_name, network.agent_name) virt_name,
  network.network_type,
  network.agent_code,
  network.agent_name,
  network.is_enable,
  network.is_suspend,
  network.area_code area_code,
  network.area_name area_name,
  network.leaf_area_id sys_area_id,
  effic_num,
  dt
from
  (
    select
      dt,
      agent_code,
      network_code,
      sum(scan_sum) effic_num
    from
      jms_dm.dm_network_taking_union_sign_hour_sum_dt
    where
       dt = '{{ execution_date | cst_ds }}'
      and type1 in ('时效签收量')
    group by
      dt,
      agent_code,
      network_code
  ) a
  left join jms_dim.dim_network_whole_massage network on network.code = a.network_code
distribute by dt,
1;


insert overwrite table jms_dm.dm_sqs_false_sign_sum_dt partition (dt)
select *
from (select nvl(statistic_date, effect.dt)                                              statistic_date,
             nvl(data_type,1) data_type,
             nvl(a.agent_code,effect.agent_code) agent_code,
             nvl(a.network_code,effect.network_code) network_code,
             delivery_code,
             nvl(a.network_name,effect.network_name) network_name,
             nvl(a.network_type,effect.network_type) network_type,
             delivery_name,
             nvl(a.agent_name, effect.agent_name)         agent_name,
             nvl(a.virt_code, effect.virt_code)           virt_code,
             nvl(a.virt_name, effect.virt_name)           virt_name,
             nvl(a.area_info_id, effect.area_info_id)     area_info_id,
             nvl(a.area_info_code, effect.area_info_code) area_info_code,
             nvl(a.area_info_desc, effect.area_info_desc) area_info_desc,
             nvl(a.provider_id, effect.provider_id)       provider_id,
             nvl(a.provider_desc, effect.provider_desc)   provider_desc,
             nvl(a.city_id, effect.city_id)               city_id,
             nvl(a.city_desc, effect.city_desc)           city_id,
             nvl(a.area_id, effect.area_id)               area_id,
             nvl(a.area_desc, effect.area_desc)           area_desc,
             nvl(a.is_enable, effect.is_enable)           is_enable,
             nvl(a.is_suspend, effect.is_suspend)         is_suspend,
             nvl(false_sign_cnt, 0)                       false_sign_cnt,
             nvl(effect.effic_num, 0)                     effic_num,
             nvl(reback_type_cnt, 0)                      reback_type_cnt,
             nvl(third_party_cnt, 0)                      third_party_cnt,
             nvl(sign_owner_cnt, 0)                       sign_owner_cnt,
             nvl(sign_other_cnt, 0)                       sign_other_cnt,
             nvl(third_party_cnt_out, 0)                  third_party_cnt_out,
             nvl(third_party_cnt_in, 0)                   third_party_cnt_in,
             nvl(thd_cnt, 0)                              thd_cnt,
             nvl(zjs_cnt, 0)                              zjs_cnt,
             nvl(qxt_cnt, 0)                              qxt_cnt,
             nvl(receive_cnt, 0)                          receive_cnt,
             nvl(no_receive_cnt, 0)                       no_receive_cnt,
             nvl(a.area_code, effect.area_code)           area_code,
             nvl(a.area_name, effect.area_name)           area_name,
             nvl(a.sys_area_id, effect.sys_area_id)       sys_area_id,
             nvl(a.delivery_order_cnt,0) delivery_order_cnt,
             nvl(a.delivery_timeout_reply_cnt,0) delivery_timeout_reply_cnt,
             nvl(a.delivery_timely_reply_cnt,0) delivery_timely_reply_cnt,
             nvl(a.dt, effect.dt)                         dt
      from (select statistic_date,
                   1 as                          data_type,
                   agent_code,
                   network_code,
                   null                          delivery_code,
                   network_name,
                   network_type,
                   null                          delivery_name,
                   agent_name,
                   virt_code,
                   virt_name,
                   area_info_id,
                   area_info_code,
                   area_info_desc,
                   provider_id,
                   provider_desc,
                   city_id,
                   city_desc,
                   area_id,
                   area_desc,
                   is_enable,
                   is_suspend,
                   sum(1)                              false_sign_cnt,
                   sum(if(false_type = 1, 1, 0))       reback_type_cnt,
                   sum(if(false_type in (5, 6), 1, 0)) third_party_cnt,
                   sum(if(false_type = 3, 1, 0))       sign_owner_cnt,
                   sum(if(false_type = 4, 1, 0))       sign_other_cnt,
                   sum(if(false_type = 5, 1, 0))       third_party_cnt_out,
                   sum(if(false_type = 6, 1, 0))       third_party_cnt_in,
                   sum(if(work_order_channel = 1 and work_order_type = 1, 1, 0))       thd_cnt,
                   sum(if(work_order_channel = 2 and work_order_type = 1, 1, 0))       zjs_cnt,
                   sum(if(work_order_channel = 3 and work_order_type = 1, 1, 0))       qxt_cnt,
                   sum(if(complaint_target = 2 and work_order_type = 2 and work_order_channel = 8, 1, 0))       receive_cnt,
                   sum(if(complaint_target <> 2 and work_order_type = 2 and work_order_channel = 8, 1, 0))      no_receive_cnt,
                   area_code,
                   area_name,
                   sys_area_id,
                   sum(is_dispatcher_order) delivery_order_cnt,
                   sum(if(is_reply = 0,1,0)) delivery_timeout_reply_cnt,
                   sum(if(is_reply = 1,1,0)) delivery_timely_reply_cnt,
                   dt
            from jms_dm.dm_sqs_false_sign_detail_dt
            where
               dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01') and dt <= '{{ execution_date | cst_ds }}'
            group by dt,
                     statistic_date,
                     agent_code,
                     network_code,
                     network_type,
                     network_name,
                     agent_name,
                     virt_code,
                     virt_name,
                     area_info_id,
                     area_info_code,
                     area_info_desc,
                     provider_id,
                     provider_desc,
                     city_id,
                     city_desc,
                     area_id,
                     area_desc,
                     is_enable,
                     is_suspend,
                     area_code,
                     area_name,
                     sys_area_id
                     ) a
               full join (select * from jms_dm.dm_false_sign_effic_sum_dt effect
                          where effect.dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                          and effect.dt <= '{{ execution_date | cst_ds }}'
               )  effect
               on a.agent_code = effect.agent_code
               and a.dt = effect.dt
               and a.network_code = effect.network_code
      union all
      select statistic_date,
             data_type,
             agent_code,
             network_code,
             delivery_code,
             network_name,
             network_type,
             delivery_name,
             agent_name,
             virt_code,
             virt_name,
             area_info_id,
             area_info_code,
             area_info_desc,
             provider_id,
             provider_desc,
             city_id,
             city_desc,
             area_id,
             area_desc,
             is_enable,
             is_suspend,
             false_sign_cnt,
             null                                                                     effic_num,
             reback_type_cnt,
             third_party_cnt,
             sign_owner_cnt,
             sign_other_cnt,
             third_party_cnt_out,
             third_party_cnt_in,
             thd_cnt,
             zjs_cnt,
             qxt_cnt,
             receive_cnt,
             no_receive_cnt,
             area_code,
             area_name,
             sys_area_id,
             delivery_order_cnt,
             delivery_timeout_reply_cnt,
             delivery_timely_reply_cnt,
             dt
      from (select statistic_date,
                   2 as                          data_type,
                   agent_code,
                   network_code,
                   delivery_code,
                   network_name,
                   network_type,
                   delivery_name,
                   agent_name,
                   virt_code,
                   virt_name,
                   area_info_id,
                   area_info_code,
                   area_info_desc,
                   provider_id,
                   provider_desc,
                   city_id,
                   city_desc,
                   area_id,
                   area_desc,
                   is_enable,
                   is_suspend,
                   sum(1)                              false_sign_cnt,
                   sum(if(false_type = 1, 1, 0))       reback_type_cnt,
                   sum(if(false_type in (5, 6), 1, 0)) third_party_cnt,
                   sum(if(false_type = 3, 1, 0))       sign_owner_cnt,
                   sum(if(false_type = 4, 1, 0))       sign_other_cnt,
                   sum(if(false_type = 5, 1, 0))       third_party_cnt_out,
                   sum(if(false_type = 6, 1, 0))       third_party_cnt_in,
                   sum(if(work_order_channel = 1 and work_order_type = 1, 1, 0))       thd_cnt,
                   sum(if(work_order_channel = 2 and work_order_type = 1, 1, 0))       zjs_cnt,
                   sum(if(work_order_channel = 3 and work_order_type = 1, 1, 0))       qxt_cnt,
                   sum(if(complaint_target = 2 and work_order_type = 2 and work_order_channel = 8, 1, 0))       receive_cnt,
                   sum(if(complaint_target <> 2 and work_order_type = 2 and work_order_channel = 8, 1, 0))      no_receive_cnt,
                   area_code,
                   area_name,
                   sys_area_id,
                   sum(is_dispatcher_order) delivery_order_cnt,
                   sum(if(is_reply = 0,1,0)) delivery_timeout_reply_cnt,
                   sum(if(is_reply = 1,1,0)) delivery_timely_reply_cnt,
                   dt
            from jms_dm.dm_sqs_false_sign_detail_dt
            where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01') and dt <= '{{ execution_date | cst_ds }}'
            group by dt,
                     statistic_date,
                     agent_code,
                     network_code,
                     network_type,
                     network_name,
                     delivery_code,
                     delivery_name,
                     agent_name,
                     virt_code,
                     virt_name,
                     area_info_id,
                     area_info_code,
                     area_info_desc,
                     provider_id,
                     provider_desc,
                     city_id,
                     city_desc,
                     area_id,
                     area_desc,
                     is_enable,
                     is_suspend,
                     area_code,
                     area_name,
                     sys_area_id) b) a distribute by dt, 1;
